// 测试数据库连接和查询
const { Pool } = require('pg')

async function testDatabase() {
  const pool = new Pool({
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '5432'),
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD || 'unidt@123',
    database: process.env.DB_NAME || 'aide',
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  })
  
  try {
    console.log('🔍 测试数据库连接...')
    const client = await pool.connect()
    
    // 1. 测试基本连接
    console.log('✅ 数据库连接成功')
    
    // 2. 检查表是否存在
    const tablesResult = await client.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      AND table_name IN ('tasks', 'users')
    `)
    console.log('📋 存在的表:', tablesResult.rows.map(r => r.table_name))
    
    // 3. 检查tasks表结构
    const columnsResult = await client.query(`
      SELECT column_name, data_type 
      FROM information_schema.columns 
      WHERE table_name = 'tasks'
      ORDER BY ordinal_position
    `)
    console.log('📊 tasks表结构:')
    columnsResult.rows.forEach(row => {
      console.log(`  - ${row.column_name}: ${row.data_type}`)
    })
    
    // 4. 检查任务数据
    const taskCountResult = await client.query('SELECT COUNT(*) as count FROM tasks')
    console.log(`📈 总任务数: ${taskCountResult.rows[0].count}`)
    
    // 5. 检查用户数据
    const userCountResult = await client.query('SELECT COUNT(*) as count FROM users')
    console.log(`👥 总用户数: ${userCountResult.rows[0].count}`)
    
    // 6. 检查用户ID分布
    const userTasksResult = await client.query(`
      SELECT user_id, COUNT(*) as count 
      FROM tasks 
      WHERE user_id IS NOT NULL 
      GROUP BY user_id 
      ORDER BY count DESC
    `)
    console.log('👤 用户任务分布:')
    userTasksResult.rows.forEach(row => {
      console.log(`  - 用户 ${row.user_id}: ${row.count} 个任务`)
    })
    
    // 7. 测试统计查询
    const userId = 'b13c9021-7b19-4115-8be2-e145768151be' // 从登录响应中获取的用户ID
    console.log(`\n🔍 测试用户 ${userId} 的统计查询:`)
    
    const totalTasksResult = await client.query('SELECT COUNT(*) as count FROM tasks WHERE user_id = $1', [userId])
    console.log(`  - 总任务数: ${totalTasksResult.rows[0].count}`)
    
    const completedTasksResult = await client.query('SELECT COUNT(*) as count FROM tasks WHERE completed = true AND user_id = $1', [userId])
    console.log(`  - 已完成任务数: ${completedTasksResult.rows[0].count}`)
    
    const overdueTasksResult = await client.query(`
      SELECT COUNT(*) as count FROM tasks 
      WHERE completed = false 
        AND due_date IS NOT NULL 
        AND due_date < NOW()
        AND user_id = $1
    `, [userId])
    console.log(`  - 逾期任务数: ${overdueTasksResult.rows[0].count}`)
    
    client.release()
    
  } catch (error) {
    console.error('❌ 数据库测试失败:', error.message)
    console.error('详细错误:', error)
  } finally {
    await pool.end()
  }
}

testDatabase()
